<?php

class Report extends Controller {

	function Report() {
		parent::Controller();
	}

	function getTelemarketing() {
		/*$iMailer = 445;
		$iMailer = 459;*/

		$iMailer = $this->input->post("mailer_id");
		$sRowSpan = 0;
		header("Content-type: application/vnd.ms-excel;charset=utf-8");
		header("Expires: 0");
		header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
		header("Content-disposition:  attachment; filename=raport.xls ");
		header("Content-type: application/vnd.ms-excel");
		header("Content-disposition:  attachment; filename=raport.xls");

		$iClient = $this->db->get_where("mailer", array("mailer_id" => $iMailer))->row()->client_id;

		//numar dezabonati
		$this->db->where("unsubscribed", 1);
		$this->db->where("client_id", $iClient);
		$iDezabonati = $this->db->get("client_persons")->num_rows();

		//total contacte
		$this->db->where("client_id", $iClient);
		$iTotalContacte = $this->db->get("client_persons")->num_rows();

		//numar emailuri
		//calcul total - contacte interne
		$sSql = "		
		SELECT 
			SUM(IF(persons.email <>  '' , 1, 0)) AS email1 ,	SUM(IF(persons.email2 <> '' , 1, 0)) AS email2 ,	COUNT(persons.person_id) as total
			FROM client_persons
			LEFT JOIN persons using(person_id) 
			WHERE client_persons.client_id = {$iClient}	AND client_persons.extern = 0";
		$aStatIntern = $this->db->query($sSql)->row_array();

		//calcul total - contacte externe
		$sSql = "		
		SELECT 
			SUM(IF(client_persons.email <>  '' , 1, 0)) AS email1 ,	SUM(IF(client_persons.email2 <> '' , 1, 0)) AS email2 ,	COUNT(client_persons.person_id) as total
			FROM client_persons
			LEFT JOIN persons using(person_id) 
			WHERE client_persons.client_id = {$iClient}	AND client_persons.extern = 1";
		$aStatExtern = $this->db->query($sSql)->row_array();


		//calcul cate emailuri s-au trimis
		$sSql = "		
		SELECT 
			SUM(IF(status = 100  , 1, 0)) AS email1_trimis_succes ,	
			SUM(IF(status2 = 100  , 1, 0)) AS email2_trimis_succes ,	
			SUM(IF(status <> 100  , 1, 0)) AS email1_netrimis ,	
			SUM(IF(status2 <> 100 , 1, 0)) AS email2_netrimis 
			FROM mailer_report		
			LEFT JOIN mailer_task on mailer_task.mailer_task_id=mailer_report.mailer_task_id
			WHERE mailer_id = {$iMailer}	";
		$aStatTrimitereEmail = $this->db->query($sSql)->row_array();


		//informatii task-uri
		$this->db->order_by("mailer_task_date asc");
		$aTask = $this->db->get_where("mailer_task", array("mailer_id" => $iMailer))->result_array();
		$aTemp = array();
		if (count($aTask) > 0) {
			foreach ($aTask as $key => $value) {
				$this->db->select("GREATEST(MAX(send_date), MAX(send_date2)) as end, LEAST(MIN(send_date), MIN(send_date2)) as start,				
											TIMEDIFF(GREATEST(MAX(send_date), MAX(send_date2)) ,LEAST(MIN(send_date), MIN(send_date2)) ) as diff  ", false);
				$this->db->where("mailer_task_id", $value["mailer_task_id"]);
				$aTemp = $this->db->get("mailer_report")->row_array();
				$sRowSpan++;
				$aTask[$key]["start"] = $aTemp["start"];
				$aTask[$key]["end"] = $aTemp["end"];
				$aTask[$key]["diff"] = $aTemp["diff"];
			}
		}
		$sRowSpan = $sRowSpan * 2;
		$iTotalEmailuri = $aStatIntern["email1"] + $aStatIntern["email2"] + $aStatExtern["email1"] + $aStatExtern["email2"];

		//numar link-uri
		$this->db->where("mailer_id", $iMailer);
		$iLinksTotal = $this->db->get("mailer_link")->num_rows();

		//volum total click-uri
		$this->db->select("count(client_person_id) as total_link,count(distinct(client_person_id)) as total_distinct_link", false);
		$this->db->where("mailer_id", $iMailer);
		$this->db->where("client_person_id !=", 1);
		$this->db->join("client_persons", "client_persons.client_persons_id=mailer_link_report.client_person_id", "inner");
		$this->db->join("mailer_link", "mailer_link.link_id=mailer_link_report.link_id", "inner");
		//$this->db->group_by("mailer_link.link_id");
		$aLinksVolum = $this->db->get("mailer_link_report")->row_array();


		//calcul pt fiecare link volum total si click-uri unice
		$this->db->where("mailer_id", $iMailer);
		$aLink = $this->db->get("mailer_link")->result_array();
		foreach ($aLink as $key => $value) {
			$this->db->select("count(client_person_id) as total_link,count(distinct(client_person_id)) as total_distinct_link", false);
			$this->db->where("link_id", $value["link_id"]);
			$this->db->where("client_person_id !=", 1);
			$aLinkStatus = $this->db->get("mailer_link_report")->row_array();
			$aLink[$key]["total_link"] = $aLinkStatus["total_link"];
			$aLink[$key]["total_distinct_link"] = $aLinkStatus["total_distinct_link"];
		}

		// Obtain a list of columns
		foreach ($aLink as $key => $row) {
		    $total_link[$key]  = $row['total_link'];
		    $total_distinct_link[$key] = $row['total_distinct_link'];
		}
		
		array_multisort($total_link, SORT_DESC, $total_distinct_link, SORT_ASC, $aLink);
		
		
		echo "<table border=1 width=1500></tr>";
		echo "<td width=500 colspan=2>Interval livrare</td>";
		echo "<td width=130>Durata in zile</td>";
		echo "<td width=130>Trimise E-mail 1 succes</td>";
		echo "<td width=130>Trimise E-mail 1 fara succes</td>";
		echo "<td width=130>Trimise E-mail 2 cu succes</td>";
		echo "<td width=130>Trimise E-mail 2 fara succes</td>";
		echo "<td width=130>Dezabonati</td>";
		echo "<td width=130>Volum baza date adrese e-mail</td>";
		echo "<td width=130>Volum baza date contacte</td>";
		echo "<tr>
			<td width=100>Start</td><td>{$aTask[0]["start"]}</td>
			<td rowspan=2>{$aTask[0]["diff"]}</td>
			<td rowspan={$sRowSpan}>{$aStatTrimitereEmail["email1_trimis_succes"]}</td>
			<td rowspan={$sRowSpan}>{$aStatTrimitereEmail["email1_netrimis"]}</td>
			<td rowspan={$sRowSpan}>{$aStatTrimitereEmail["email2_trimis_succes"]}</td>
			<td rowspan={$sRowSpan}>{$aStatTrimitereEmail["email2_netrimis"]}</td>
			<td rowspan={$sRowSpan}>{$iDezabonati}</td>
			<td rowspan={$sRowSpan}>{$iTotalEmailuri}</td>
			<td rowspan={$sRowSpan}>{$iTotalContacte}</td>			
			</tr>";
		echo "<tr><td>Stop</td><td>{$aTask[0]["end"]}</td></tr>";

		foreach ($aTask as $key => $value) {
			if ($key == 0)
				continue;
			echo "<tr>
			<td>Start</td><td>{$aTask[0]["start"]}</td>
			<td rowspan=2>{$aTask[0]["diff"]}</td>";
			echo "<tr><td>Stop</td><td>{$aTask[0]["end"]}</td></tr>";
		}

		echo "<tr>
			<td colspan=2>Sumar Click</td>
			<td>Volum Click-uri</td>
			<td>Unici - click</td>
			<td>Numar Link-uri </td>					
			</tr>";
		echo "<tr>
			<td colspan=2>Total</td>
			<td>{$aLinksVolum["total_link"]}</td>
			<td>{$aLinksVolum["total_distinct_link"]}</td>
			<td>{$iLinksTotal}</td>					
			</tr>";
		echo "<tr>
			<td colspan=2 >	top volum clik pe link-ui top in ordinea descrescatoare cele mai accesate link-uri</td>			
			<td>Volum Click-uri Total</td>
			<td>Volum Click-uri Unici</td>
			</tr>";

		foreach ($aLink as $key => $value) {
			echo "<tr>
					<td>{$value["link_id"]}</td>
					<td>{$value["link_nume"]}</td>
					<td>{$value["total_link"]}</td>
					<td>{$value["total_distinct_link"]}</td>									
					</tr>";
		}

		echo "</tr></table>";
		echo $report;


	}

}

/* End of file report.php */
/* Location: ./system/application/controllers/report.php */
